package com.yellowpage.wf.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.yellowpage.white.common.Constants;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;

import com.yellowpage.wf.data.Worklist;
import com.yellowpage.wf.dao.WorklistDao;

public class WorklistServiceImpl extends BaseServiceImpl implements
		WorklistService {
	private WorklistDao worklistDao;

	public void setWorklistDao(WorklistDao worklistDao) {
		this.worklistDao = worklistDao;
	}

	/* White generate Method */
	public Worklist getWorklist(Long id) {
		return worklistDao.get(id);
	}

	public List<Worklist> getAllWorklist() {
		return worklistDao.getAll();
	}

	public List<Map> queryWorklistByPage(Map queryMap, PageRender pageRender) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT a.WF_WORKLIST_ID , a.WF_WORKHISTORY_ID , a.HANDLERS , a.STATUS    FROM WF_WORKLIST a WHERE 1=1 ")
				.addCondition(" and WF_WORKLIST_ID = :wfWorklistId ")
				.addCondition(" and WF_WORKHISTORY_ID = :wfWorkhistoryId ")
				.addCondition(" and HANDLERS = :handlers ").addCondition(
						" and STATUS = :status ");

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}
	
	public List<Map> getWorklistByUserName(String userName) {
		Map queryMap = new HashMap();	
	    queryMap.put("userName", userName);

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT WF.NAME as WORKFLOW_NAME, WI.WORKFLOW_INSTANCE_ID,  WFN.NAME as WORKFLOW_NODE_NAME, WFN.DISPLAY_NAME  as WORKFLOW_NODE_DISPLAY_NAME, WNI.NODE_INSTANCE_ID" +
						"  FROM WF_WORKFLOW WF, WF_WORKFLOW_NODE WFN, WF_WORKFLOW_INSTANCE WI," +
						"  WF_PARTICIPANTS WP, WF_WORKFLOW_NODE_INSTANCE WNI " +
						" WHERE WI.WORKFLOW_NODE_ID = WP.WORKFLOW_NODE_ID" +
						"   AND WFN.WORKFLOW_NODE_ID = WI.WORKFLOW_NODE_ID" +
						"   AND WFN.WORKFLOW_ID = WF.WORKFLOW_ID" +
						"   AND WNI.WORKFLOW_NODE_ID = WI.WORKFLOW_NODE_ID" +
						"   AND WNI.WORKFLOW_INSTANCE_ID = WI.WORKFLOW_INSTANCE_ID" +
						"   AND WP.PARTICIPANT_TYPE = 'USER'" +
						"   AND WI.STATUS = 'on' ")
				.addCondition("   AND WP.PARTICIPANT_VALUE = :userName ");

		List<Map> resultList = sqlAssistant.query(
				baseDao.getConnection(), queryMap);

		return resultList;
	}
	
	
	public List<Map> getSignedTaskList(String userId){
		String sql = 

			" select distinct t.workflow_instance_id , t.handlers , to_char(wi.create_time,'yyyy-MM-dd hh24:mi:ss') apply_time , to_char(wi.LAST_UPDATE_TIME,'yyyy-MM-dd hh24:mi:ss') as update_time , wi.name as i_name,   (select e.name from employee e where e.employee_id||'' = t.handlers) as E_NAME , "+
			" (select node.name from wf_workflow_instance b , wf_workflow_node node , wf_workflow_node_instance ni where ni.workflow_history_id = b.workflow_node_id "+
			" and node.workflow_node_id = ni.workflow_node_id and ni.workflow_instance_id = t.workflow_instance_id) NODE_NAME, "+
			" (select con.param_value as href from wf_context con where con.param_name = 'HREF' and con.instance_id = wi.workflow_instance_id ) as href, "+
			" (select con.param_value as nversion from wf_context con where con.param_name = 'VERSION' and con.instance_id = wi.workflow_instance_id ) as nversion, "+
			" (select con.param_value as delegate from wf_context con where con.param_name = 'DELEGATE' and con.instance_id = wi.workflow_instance_id ) as delegate "+
			" from wf_worklist t , wf_workflow_instance wi where t.workflow_instance_id = wi.workflow_instance_id and t.status = 'END' and t.handlers = '@' order by update_time desc ";
		sql = sql.replaceAll("@", userId);
		
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(sql);

		List<Map> resultList = this.baseDao.query(sqlAssistant, new HashMap());
		
		return resultList;
	}

	public List<Map> getTaskList(String userId, String status) {
		
		String sql = " select w.*, i.name as i_name,i.WORKFLOW_INSTANCE_ID ,i.status as i_status, to_char(i.create_time,'yyyy-MM-dd hh24:mi:ss') as apply_time , to_char(i.LAST_UPDATE_TIME,'yyyy-MM-dd hh24:mi:ss') as update_time , "
				+ " (select con.param_value as href from wf_context con where con.param_name = 'HREF' and con.instance_id = w.workflow_instance_id ) as href, "
				+ " (select con.param_value as nversion from wf_context con where con.param_name = 'VERSION' and con.instance_id = w.workflow_instance_id ) as nversion, "
				+ " (select con.param_value as delegate from wf_context con where con.param_name = 'DELEGATE' and con.instance_id = w.workflow_instance_id ) as delegate, "
				+ " (select e.name from employee e where e.employee_id = @) as e_name ,   "
				+ "    nvl((select n.name "
				+ "     from wf_workflow_node n "
				+ "    where n.workflow_node_id = wwni.workflow_node_id),'开始结点') as node_name "
				+ " from wf_worklist w ,  wf_workflow_instance i , wf_workflow_node_instance wwni  "
				+ "  where w.workflow_instance_id = i.workflow_instance_id and  w.wf_workhistory_id = wwni.workflow_history_id(+)  and w.status = '"
				+ status
				+ "'   "
				+ " and ',' || w.handlers || ',' like '%,@,%' order by update_time desc ";
		

		sql = sql.replaceAll("@", userId);

		
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(sql);

		List<Map> resultList = this.baseDao.query(sqlAssistant, new HashMap());
		
		return resultList;
	}

	public List<Map> getHistoryInfo(String instanceId) {
		// String sql =
		// "select w.*,(select e.name from employee e where e.employee_id = 4) as e_name from wf_worklist w where w.status = 'PROCESSING' and ','||w.handlers||',' like '%,4,%'";
		// String sql =
		// "select t.*,r.*,(select e.name from employee e where e.employee_id = t.handlers) as e_name,i.name as i_name ,to_char(i.create_time,'yyyy-MM-dd hh24:mi:ss') as apply_time, i.status as i_status from wf_worklist t , wf_workflow_node_remark r , wf_workflow_instance i where i.workflow_instance_id = t.workflow_instance_id and t.wf_workhistory_id = r.workflow_history_id and t.status = 'END' and t.workflow_instance_id =  "
		// + instanceId;
		String sql = ""
				+ "  select t.*, "
				+ "    r.*, decode(r.audit_result , 'AUDIT_YES' , '审批通过' , '审批不通过') as AUDIT_C_RESULT , "
				+ "    (select e.name "
				+ "      from employee e "
				+ "       where e.employee_id = t.handlers) as e_name, "
				+ "    (select n.name "
				+ "     from wf_workflow_node n "
				+ "    where n.workflow_node_id = wwni.workflow_node_id) as node_name,"
				+ "    i.name as i_name, "
				+ "    to_char(i.create_time, 'yyyy-MM-dd hh24:mi:ss') as apply_time, "
				+ "    i.status as i_status "
				+ "     from wf_worklist             t, "
				+ "    wf_workflow_node_remark r, "
				+ "    wf_workflow_instance    i , wf_workflow_node_instance wwni "
				+ "    where i.workflow_instance_id = t.workflow_instance_id and wwni.workflow_history_id = t.wf_workhistory_id "
				+ "      and t.wf_workhistory_id = r.workflow_history_id and t.status = 'END' and t.workflow_instance_id =  "
				+ instanceId;

		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(sql);

		// List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
		// new HashMap());
		// System.out.println("resultList.size() : +++++++++ " +
		// resultList.size());
		// List<Map> resultList = this.baseDao.query(sql , new HashMap());
		List<Map> resultList = this.baseDao.query(sqlAssistant, new HashMap());
		return resultList;
	}
	
	
	public List<Map> getContextByNodeInstance(Long nodeInstanceId){
		String sql = "select c.context_id, c.param_name, c.default_value, c.param_type," +
				"       ci.param_value, ci.version, ni.node_instance_id" +
				"  from wf_context_instance ci, wf_context c, " +
				"       wf_workflow_node_instance ni, wf_workflow_instance wi" +
				" where c.context_id = ci.context_id(+)" +
				"   and c.workflow_id = wi.workflow_id" +
				"   and wi.workflow_instance_id =  ni.workflow_instance_id" +
				"   and ni.node_instance_id =" + nodeInstanceId.toString() +
				"   and nvl(ci.version,0) = nvl((select max(wcii.version) from wf_context_instance wcii" +
				" where wcii.context_id = ci.context_id),0) and ci.context_id(+) = c.context_id ";

	    QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(sql);
		List<Map> resultList = this.baseDao.query(sqlAssistant, new HashMap());
		return resultList;
	}
	
	public List<Map> getNodeContextByNodeInstance(Long nodeInstanceId){
		String sql = "select nc.node_context_id, nc.param_name, nc.default_value, nc.param_type, " +
				" nci.param_value, nci.version, wni.node_instance_id " +
				" from wf_node_context nc, wf_node_context_instance nci, wf_workflow_node_instance wni " +
				" where nc.node_context_id = nci.node_context_id(+) " +
				" and wni.workflow_node_id = nc.workflow_node_id " +
				" and wni.node_instance_id =" + nodeInstanceId.toString() +
				" and nvl(nci.version,0) = nvl((select max(ncii.version) from wf_node_context_instance ncii" +
				" where ncii.node_context_id = nci.node_context_id),0) and nci.node_context_id(+) = nc.node_context_id ";
		
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(sql);
		List<Map> resultList = this.baseDao.query(sqlAssistant, new HashMap());
		return resultList;
	}
	
	public List<Map> getAttachmentByNodeInstance(Long nodeInstanceId){
		String sql = "select wa.attachment_id,wa.name,wa.type,wa.description,wai.note,wai.attachment_instance_id,wai.version,ni.node_instance_id, " +
				" wr.resource_id,wr.name as fname,wr.type as ftype,wr.dir " +
				" from wf_resource wr,wf_attachment_instance wai, wf_attachment wa,wf_workflow_node_instance ni,wf_workflow_instance wi " +
				"  where wa.attachment_id = wai.attachment_id(+) " +
				" and wa.workflow_id = wi.workflow_id " +
				" and wi.workflow_instance_id = ni.workflow_instance_id " +
				" and ni.node_instance_id =" + nodeInstanceId.toString() +
				" and nvl(wai.version, 0) = nvl((select max(waii.version) " +
				" from wf_attachment_instance waii " +
				" where waii.attachment_id = wai.attachment_id),0) " +
				" and wai.attachment_id(+) = wa.attachment_id " +
				" and wa.resource_id = wr.resource_id ";
		
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(sql);
		List<Map> resultList = this.baseDao.query(sqlAssistant, new HashMap());
		return resultList;
	}
	
	public List<Map> getNodeAttachmentByNodeInstance(Long nodeInstanceId){
		String sql = "select wna.node_attachment_id,wna.name,wna.type,wna.description,wnai.node_attachment_instance_id,wnai.note,wnai.version, " +
				" wni.node_instance_id,wr.resource_id,wr.name as fname,wr.type as ftype,wr.dir " +
				" from wf_resource wr,wf_node_attachment_instance wnai,wf_node_attachment wna,wf_workflow_node_instance wni " +
				" where wna.node_attachment_id = wnai.node_attachment_id(+) " +
				" and wni.workflow_node_id = wna.workflow_node_id " +
				" and wni.node_instance_id =" + nodeInstanceId.toString() +
				" and nvl(wnai.version, 0) = nvl((select max(wnaii.version) " +
				" from wf_node_attachment_instance wnaii " +
				" where wnaii.node_attachment_id = wnai.node_attachment_id),0) " +
				" and wnai.node_attachment_id(+) = wna.node_attachment_id " +
				" and wna.resource_id = wr.resource_id ";
		
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(sql);
		List<Map> resultList = this.baseDao.query(sqlAssistant, new HashMap());
		return resultList;
	}
	

	public void saveWorklist(Worklist worklist) {
		worklistDao.save(worklist);
	}

	public void updateWorklist(Worklist worklist) {
		worklistDao.update(worklist);
	}

	public void delete(List deleteList) {
		worklistDao.remove(deleteList);
	}

}